Certain databases support auto-generation of field values, where the auto-generated value is a result of some other record insertion into the database. For example, when an Order row is inserted into the Order table, the OrderID, which is a unique sequence number, is generated by the database. This field containing the OrderID is a database-generated field.

Sequencing Object Insertion in WS-AppServer

When writing to a database, it is useful to identify the object with a unique number. To enable this, you need to have a process in place that increments the count of objects each time you insert an object into the database. Each request will possess a unique identifier. However, the situation will vary depending upon whether the request is for inserting an object or for inserting multiple objects.

SINGLE REQUEST INSERTING A SINGLE OBJECT
You can sequence each single object (per request) in the database by placing the appropriate command in the OnBeforeInsert event listener in the extension class. You can use the following sample code for this purpose.

public void onBeforeInsert()
{
  String queryText = "select max(adjustment_order) as maxNr" + "from stock_adjustment " + "where center = :center";
  QueryObject query = new QueryObject(queryText);
  query.addParameter("center", "stock_adjustment.center", QueryObject.PARAM_INT, new Short(this.getCenter()));
  AnonymousBusObject aBusObj = (AnonymousBusObject)query.getObject();
  // And add 1 for the new record
  this.setAdjustment_Order(aBusObj.getIntProperty("maxNr")+1);
}

SINGLE REQUEST INSERTING MULTIPLE OBJECTS
If the request contains multiple objects to be inserted, as a result of optimistic transaction mechanism, each object may get the same identifying sequence number. To prevent this problem, take the following steps:

1. Retrieve the maximum value of the sequence number from the database (maxDB)
   
2. Retrieve the maximum number of objects in the current transaction (maxTrans)
   
3. Retrieve the maximum value of maxDB and maxTrans (maxSeqNo)
   
4. Increment maxSeqNo by 1 to get the new sequence number.
   For example,

   /**
    * Gets the next available Stock Adjustment Order number for the given center.
    * 
    * @param center
    *            The center
    * @return the next stock adjustment order number
    */
    
   private int getNextAdjustmentOrderNumber(short center)
   {
     // Check if there is already a StockAdjustment in
     // the transaction (i.e. the BusObjectManager)
     int transactionMaxOrderNo = 0;
     short sCenter = this.getCenter();
     BusObjectIterator objects = BSF.getObjectManager().getUpdatedObjects();
    
     while ( objects.hasMoreElements() )
     {
       BusObject busObject = objects.nextElement();
       if ( busObject instanceof StockAdjustment )
       {
         StockAdjustment stckAdj = (StockAdjustment)busObject;
         if ( stckAdj != this && sCenter == stckAdj.getCenter() )
         {
           int orderNo = stckAdj.getAdjustment_order();
           if ( orderNo > transactionMaxOrderNo )
           {
             transactionMaxOrderNo = orderNo;
           }
         }
       }
     }
    
     // Get max seqno from DB
     int dbMaxOrderNo = 0;
     String queryText = "select max(adjustment_order) as maxNr" + "from stock_adjustment " + "where center = :center";
     QueryObject query = new QueryObject(queryText);
     query.addParameter("center", "stock_adjustment.center", QueryObject.PARAM_INT, new Short(this.getCenter()));
     AnonymousBusObject aBusObj = (AnonymousBusObject)query.getObject();
    
     // And add 1 for the new record
     dbMaxOrderNo = aBusObj.getIntProperty("maxNr");
     if ( transactionMaxOrderNo > dbMaxOrderNo )
     {
       return transactionMaxOrderNo+1;
     }
     else
     {
       return dbMaxOrderNo+1;
     }
   }

   
   MULTIPLE REQUESTS INSERTING SINGLE OR MULTIPLE OBJECTS
   
   If there are multiple requests attempting to insert one or more objects at the same time, there is a possibility that some objects would get the same identity sequence number, resulting in a primary key error. To avoid such a problem, take the following preventive measures:
   
   

• Use the automatic retry feature that will inevitably re-calculate the numbers.
  
• Process a single insert operation at a time.
  
• Use a different algorithm to calculate the sequence number (for example, you can have a different database table to store the last-used sequence number).
  

Attribute Value Integrity

Transactions within WS-AppServer are optimistic transactions. If a database-generated field needs to be created, it should always be after an object is committed to the database. Therefore, any logic that creates a database-generated field should be placed in the OnAfterCommit event listener.

Since the database-generated value is available only after commit, special provision should be made to use this value for other objects in the same transaction. A typical example is the use of a Foreign Key (FK) to associate objects to each other. For example, in an Order/OrderLine situation, OrderLines are associated to an Order object by sharing the same OrderID value (in an RDBMS scenario, an OrderLine has an FK to the Order's Primary Key (PK)). To handle such situations, WS-AppServer establishes dependency among certain attributes. This is called attribute value integrity.
Currently, you have to write the code to define the integrity dependency. Instead, WS-AppServer provides you with an API (of BusObject) that can be invoked on the object dependent upon the source object:

public void setAttributeValueIntegrity
(  
  BusObject sourceObject,
  String sourceAttributeName,
  String attributeName
)

Referring to the Order/OrderLines example, the method is called on the OrderLine object because it is dependent upon the source object Order. As per the example, the sourceAttributeName is the attribute in the sourceObject (PK), the attributeName is the attribute name in the depending object (FK), and the sourceObject is an Order object If attribute value integrity is required for multiple attributes, you can use the following API:

public void setAttributeValueIntegrity
(  
  BusObject sourceObject,
  String[] sourceAttributeNames,
  String[] attributeNames
)

Note: This functionality is based on the Associated Insert feature offered by the XQY Library. For more information, refer to Handling Aggregated Updates and Associated Inserts.